package br.ufpi.die.cSql.teste;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Teste {
	public Teste () {
		
	}
	static int quantidadeAtributos = 3;
    Connection conexao;

    public Teste(String db_file_name_prefix) throws Exception {

        Class.forName("org.hsqldb.jdbcDriver");

        conexao = DriverManager.getConnection("jdbc:hsqldb:"
                                           + db_file_name_prefix,    // filenames
                                           "sa",                     // username
                                           "");                      // password
    }

    public void shutdown() throws SQLException {

        Statement st = conexao.createStatement();

        st.execute("SHUTDOWN");
        conexao.close();
    }

    // SELECT
    public synchronized ArrayList<String[]> query(String expressao) throws SQLException { //ARRAYLIST!!!!!!!!

        Statement st = null;
        ResultSet rs = null;
        
        st = conexao.createStatement();         // statement objects can be reused with
        
        // repeated calls to execute but we
        // choose to make a new one each time
        rs = st.executeQuery(expressao);    // run the query

        // do something with the result set.
        ArrayList<String[]> resultado =  dump(rs);
        st.close();    // NOTE!! if you close a statement the associated ResultSet is
        
        return resultado;

        // closed too
        // so you should copy the contents to some other object.
        // the result set is invalidated also  if you recycle an Statement
        // and try to execute some other query before the result set has been
        // completely examined.
    }

    // CREATE, DROP, INSERT e UPDATE
    public synchronized void update(String expression) throws SQLException {

        Statement st = null;

        st = conexao.createStatement();    // statements

        int i = st.executeUpdate(expression);    // run the query

        if (i == -1) {
            System.out.println("db error : " + expression);
        }

        st.close();
    }    // void update()

    public static ArrayList<String[]> dump(ResultSet rs) throws SQLException {

        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta   = rs.getMetaData();
        int               colmax = meta.getColumnCount();
        int               i;
        Object            o = null;
        String string = "";
        ArrayList<String[]> resultado = new ArrayList<String[]>();
        String[] valores = new String[quantidadeAtributos]; 
        // the result set is a cursor into the data.  You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next(); ) {
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);    // Is SQL the first column is indexed

                // with 1 not 0
                //resultado.add(o.toString());
                //string = string + o.toString() + " ";
                
                valores[i] = o.toString();
                
            }

            //resultado.add(string);
            resultado.add(valores);
            
            string = "";
            valores = new String[quantidadeAtributos];
        }
    return resultado;    
    }                                       //void dump( ResultSet rs )
    private static void dropTabela() {
    	Teste db = null;

        try {
            db = new Teste("db_file");
        } catch (Exception ex1) {
            ex1.printStackTrace();    // could not start db

            return;                   // bye bye
        }
    	try {

            //make an empty table
            //
            // by declaring the id column IDENTITY, the db will automatically
            // generate unique values for new rows- useful for row keys
            db.update(
                "DROP TABLE tabela");
        } catch (SQLException ex2) {

            //ignore
            //ex2.printStackTrace();  // second time we run program
            //  should throw execption since table
            // already there
            //
            // this will have no effect on the db
        }
    }
    public static void povoarBanco() {
    	Teste db = null;

        try {
            db = new Teste("db_file");
        } catch (Exception ex1) {
            ex1.printStackTrace();    // could not start db

            return;                   // bye bye
        }
        try {

            //make an empty table
            //
            // by declaring the id column IDENTITY, the db will automatically
            // generate unique values for new rows- useful for row keys
        	dropTabela();
            db.update(
                "CREATE TABLE tabela ( id INTEGER IDENTITY, valor_coluna VARCHAR(256), numero_coluna INTEGER)");
        } catch (SQLException ex2) {

            //ignore
            //ex2.printStackTrace();  // second time we run program
            //  should throw execption since table
            // already there
            //
            // this will have no effect on the db
        }
        try {

            // add some rows - will create duplicates if run more then once
            // the id column is automatically generated
            db.update(
                "INSERT INTO tabela(valor_coluna,numero_coluna) VALUES('Ford', 100)");
            db.update(
                "INSERT INTO tabela(valor_coluna,numero_coluna) VALUES('Toyota', 200)");
            db.update(
                "INSERT INTO tabela(valor_coluna,numero_coluna) VALUES('Honda', 300)");
            db.update(
                "INSERT INTO tabela(valor_coluna,numero_coluna) VALUES('GM', 400)");
            
            
            // at end of program
            db.shutdown();
        } catch (SQLException ex2) {
            ex2.printStackTrace();
        }
    }
    
    public static ArrayList<String[]> fazQuery(String args) {
    	ArrayList<String[]>  resultadoQuery = new ArrayList<String[]>();
        Teste db = null;

        try {
            db = new Teste("db_file");
        } catch (Exception ex1) {
            ex1.printStackTrace();    // could not start db

            return null;                   // bye bye
        }

        try {

            // do a query
            resultadoQuery = db.query(args);

            // at end of program
            db.shutdown();
        } catch (SQLException ex3) {
            ex3.printStackTrace();
        }
        
        return resultadoQuery;
    } 

}    // class Testdb